﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MMSA.Services.SharedServices;
using System.Data;
using SmartProcess.Core.Commons.Tracing;
using System.Configuration;
using System.Data.SqlClient;

namespace MMSA.Services.Services
{
    public class WeeklyFrequencyManagementService
        :IDataManagementService
    {
        private TracingService tracing = new TracingService("MMSA.Services");
        string traceServicio = string.Empty;
        public void MassiveProductUpload(string fileName, string fileExtension)
        {
            try
            {
                var dataTable = new DataTable("RUT_FRECUENCIA_SEMANAL");

                traceServicio = "Antes de cargar desde archivo excel: " + fileName;

                dataTable.GetDataFromExcelSpreadsheet(fileName, fileExtension);
                MMSA.Entities.WeeklyFrequency.RUT_FRECUENCIA_SEMANALDataTable monthlyFrequency = new Entities.WeeklyFrequency.RUT_FRECUENCIA_SEMANALDataTable();

                traceServicio = "Antes de recorrer las columnas de lugares";

                foreach (DataRow item in dataTable.Rows)
                {
                    var row = monthlyFrequency.NewRUT_FRECUENCIA_SEMANALRow();
                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        Boolean flag = true;
                        traceServicio = "Se cayo al leer la Columna: " + dataTable.Columns[i].ColumnName + " ,revise si el archivo subido pertenece al formato correcto.";


                        if (dataTable.Columns[i].ColumnName.Equals("frs_repetir") ||
                            dataTable.Columns[i].ColumnName.Equals("frs_lunes") ||
                            dataTable.Columns[i].ColumnName.Equals("frs_martes") ||
                            dataTable.Columns[i].ColumnName.Equals("frs_miercoles") ||
                            dataTable.Columns[i].ColumnName.Equals("frs_jueves") ||
                            dataTable.Columns[i].ColumnName.Equals("frs_viernes") ||
                            dataTable.Columns[i].ColumnName.Equals("frs_sabado") ||
                            dataTable.Columns[i].ColumnName.Equals("frs_domingo") ||
                            dataTable.Columns[i].ColumnName.Equals("frd_sin_fin") ||
                            dataTable.Columns[i].ColumnName.Equals("frs_semana1") ||
                            dataTable.Columns[i].ColumnName.Equals("frs_semana2") ||
                            dataTable.Columns[i].ColumnName.Equals("frs_semana3") ||
                            dataTable.Columns[i].ColumnName.Equals("frs_semana4") ||
                            dataTable.Columns[i].ColumnName.Equals("frs_semana5") ||
                            dataTable.Columns[i].ColumnName.Equals("frs_semana6"))
                        {
                            if (item[dataTable.Columns[i].ColumnName].Equals(1.0))
                            {
                                row[dataTable.Columns[i].ColumnName] = Convert.ChangeType(1,
                               Type.GetType("System.Boolean"));
                                flag = false;
                            }
                            if (item[dataTable.Columns[i].ColumnName].Equals("NULL"))
                            {
                                flag = false;
                                row[dataTable.Columns[i].ColumnName] = Convert.ChangeType(false,
                               Type.GetType("System.Boolean"));
                            }
                            else
                            {
                                flag = false;
                                row[dataTable.Columns[i].ColumnName] = Convert.ChangeType(false,
                               Type.GetType("System.Boolean"));
                            }

                            
                        }
                        
                        if (flag)
                        {

                            if (item[dataTable.Columns[i].ColumnName].Equals("NULL"))
                            {
                                if (dataTable.Columns[i].ColumnName.Equals("frs_repetir")||
                                    dataTable.Columns[i].ColumnName.Equals("frd_fin_rep")||
                                    dataTable.Columns[i].ColumnName.Equals("frs_repetir")||
                                    dataTable.Columns[i].ColumnName.Equals("loc_id"))
                                {
                                
                                    row[dataTable.Columns[i].ColumnName] = Convert.ChangeType(0, Type.GetType("System.Int32"));
                                }

                                    

                                else {
                                    if (dataTable.Columns[i].ColumnName.Equals("frd_inicio")||
                                        dataTable.Columns[i].ColumnName.Equals("frd_fin"))
                                    {
                                        row[dataTable.Columns[i].ColumnName] = Convert.ChangeType(System.DateTime.Today, Type.GetType("System.DateTime"));//no preguntar q va por default
                                    }
                                    else {
                                        row[dataTable.Columns[i].ColumnName] = Convert.ChangeType("", Type.GetType("System.String"));
                                    
                                    }
                                
                                        
                                
                                }



                                    
                            }
                            else
                            {
                                row[dataTable.Columns[i].ColumnName] = Convert.ChangeType(item[dataTable.Columns[i].ColumnName], Type.GetType("System.String"));
                                //annualFrequency.Columns[dataTable.Columns[2].ColumnName].DataType);


                            }


                        }
                    }
                    monthlyFrequency.AddRUT_FRECUENCIA_SEMANALRow(row);
                  
                }
                traceServicio = "Antes de generar el upsert de actualización e inserción.";
                
                InsertOrUpdate1(monthlyFrequency);
            }
            catch (Exception exception)
            {
                if (exception != null)
                    tracing.ErrorFormat("MMSA.Services.Services.WeeklyFrequencyManagementService ==> TRACE: {0}, ==> ERROR: {1}, ==> STACKTRACE: {2}", traceServicio, exception.Message, exception.StackTrace);
                throw;
            }
        }


        private static Dictionary<Type, SqlDbType> typeReference;

        public static void loadDictionary()
        {
            typeReference = new Dictionary<Type, SqlDbType>();
            typeReference.Add(typeof(string), SqlDbType.NVarChar);
            typeReference.Add(typeof(Guid), SqlDbType.UniqueIdentifier);
            typeReference.Add(typeof(long), SqlDbType.BigInt);
            typeReference.Add(typeof(byte[]), SqlDbType.Binary);
            typeReference.Add(typeof(bool), SqlDbType.Bit);
            typeReference.Add(typeof(DateTime), SqlDbType.DateTime);
            typeReference.Add(typeof(decimal), SqlDbType.Decimal);
            typeReference.Add(typeof(double), SqlDbType.Float);
            typeReference.Add(typeof(int), SqlDbType.Int);
            typeReference.Add(typeof(float), SqlDbType.Real);
            typeReference.Add(typeof(short), SqlDbType.SmallInt);
            typeReference.Add(typeof(byte), SqlDbType.TinyInt);
            typeReference.Add(typeof(object), SqlDbType.Udt);
            typeReference.Add(typeof(DataTable), SqlDbType.Structured);
            typeReference.Add(typeof(DateTimeOffset), SqlDbType.DateTimeOffset);

        }

        public void InsertOrUpdate1(DataTable data)
        {
            loadDictionary();
            var conection = ConfigurationManager.AppSettings["conection"];
            SqlConnection conn = new SqlConnection(conection);
            conn.Open();
            foreach (DataRow row in data.Rows)
            {
                var query = GetInsertOrUpdateString((int)row["frs_id"] > 0);
                var command = new SqlCommand(query, conn);

                foreach (DataColumn column in data.Columns)
                {
                    var param = new SqlParameter(column.ColumnName.Replace('/', '_'), typeReference[column.DataType]);
                    param.Value = row[column.ColumnName];
                    command.Parameters.Add(param);
                }
                command.ExecuteNonQuery();

            }
            conn.Close();
        }

        public string GetInsertOrUpdateString(bool insertIdentity = false)
        {

            var identityInsertOn = " SET IDENTITY_INSERT RUT_FRECUENCIA_SEMANAL ON; ";
            var identityInsertOff = " SET IDENTITY_INSERT RUT_FRECUENCIA_SEMANAL OFF; ";
            var query = ((insertIdentity) ? identityInsertOn : String.Empty) + @" IF NOT EXISTS (SELECT * FROM RUT_FRECUENCIA_SEMANAL 
                   WHERE frs_id = @frs_id)
                BEGIN " +
                    @"INSERT INTO RUT_FRECUENCIA_SEMANAL (" + ((insertIdentity) ? "frs_id," : string.Empty) + @"frs_repetir, frs_lunes, frs_martes, frs_miercoles, frs_jueves, frs_viernes, frs_sabado, frs_domingo, "
                    + @"frd_inicio, frd_fin,frd_fin_rep, frd_sin_fin, loc_id, frs_semana1, frs_semana2, frs_semana3, frs_semana4, frs_semana5, frs_semana6)
                    VALUES(" + ((insertIdentity) ? "@frs_id," : string.Empty) + @"@frs_repetir,@frs_lunes,@frs_martes,@frs_miercoles,@frs_jueves,@frs_viernes,@frs_sabado,@frs_domingo,@frd_inicio,@frd_fin,@frd_fin_rep,@frd_sin_fin,@loc_id,@frs_semana1,@frs_semana2,@frs_semana3,@frs_semana4,@frs_semana5,@frs_semana6)"

                    +
                    @"END
                ELSE
                BEGIN
                    UPDATE RUT_FRECUENCIA_SEMANAL
                    SET frs_repetir=@frs_repetir,frs_lunes=@frs_lunes,frs_martes=@frs_martes,frs_miercoles=@frs_miercoles,frs_jueves=@frs_jueves,frs_viernes=@frs_viernes,frs_sabado=@frs_sabado,frs_domingo=@frs_domingo,frd_inicio=@frd_inicio,frd_fin=@frd_fin,frd_fin_rep=@frd_fin_rep,frd_sin_fin=@frd_sin_fin,loc_id=@loc_id,frs_semana1=@frs_semana1,frs_semana2=@frs_semana2,frs_semana3=@frs_semana3,frs_semana4=@frs_semana4,frs_semana5=@frs_semana5,frs_semana6=@frs_semana6
                    WHERE  frs_id = @frs_id
                END" + ((insertIdentity) ? identityInsertOff : String.Empty);
            return query;
        }

    }




}
